﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Net;
using System.Net.Mail;
using System.Text;
using System.IO;
using System.Runtime.InteropServices;
using AKMII.DMRA.DataAccess;
using Process = System.Diagnostics.Process;

using AKMII.DMRA.Common;

using Microsoft.Office.Interop.Excel;
using AKMII.DMRA.Business.Rule;

namespace AKMII.DMRA.DataLoader
{
    public class Utility
    {
        public static string GetFileName(string filePath)
        {
            if (System.IO.File.Exists(filePath))
            {
                FileInfo file = new FileInfo(filePath);
                int extLength = file.Name.Length - file.Name.LastIndexOf(Constants.DOT);
                return file.Name.Substring(0, file.Name.Length - extLength);
            }
            else
            {
                return null;
            }
        }

        public static string GetFileExtension(string filePath)
        {
            if (System.IO.File.Exists(filePath))
            {
                FileInfo file = new FileInfo(filePath);
                int extLength = file.Name.Length - file.Name.LastIndexOf(Constants.DOT);
                return file.Name.Substring(file.Name.Length - extLength, extLength);
            }
            else
            {
                return null;
            }
        }

        public static string GetFileTimestamp()
        {
            return DateTime.Now.ToString("_yyyyMMdd_hhmmss");
        }

        public static bool PathEndWithBackSlash(string path)
        {
            string lastChar = path.Substring(path.Length - 1, 1);
            if (lastChar == Constants.BACK_SLASH)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static string RemoveStringBlank(string line)
        {
            if (!string.IsNullOrEmpty(line))
            {
                while (line[line.Length - 1] == ' ')
                {
                    line = line.Remove(line.Length - 1, 1);
                }
            }

            return line;
        }

        public static string GetNumber(int i)
        {
            string number = string.Empty;

            if (i > 0)
            {
                if (i < 10)
                {
                    number = "0" + i.ToString();
                }
                else
                {
                    number = i.ToString();
                }
            }

            return number;
        }

        public static void GenerateReport(List<List<Order>> allOrders, List<string> names, string path)
        {
            Application excelApplication = null;
            Workbook workbook = null;
            Sheets sheets = null;
            Worksheet worksheet = null;
            object o = System.Reflection.Missing.Value;

            try
            {
                if (allOrders != null && allOrders.Count > 0)
                {
                    excelApplication = new Application();
                    workbook = excelApplication.Workbooks.Add(o);

                    for (int i = 0; i < allOrders.Count - 3; i++)
                    {
                        workbook.Worksheets.Add(o, o, o, o);
                    }

                    sheets = workbook.Worksheets;
                    for (int i = 0; i < allOrders.Count; i++)
                    {
                        if (allOrders[i] != null)
                        {
                            worksheet = (Worksheet)sheets[i + 1];
                            worksheet.Name = names[i];

                            worksheet.Cells[1, 1] = "Marketing Director Name";
                            worksheet.Cells[1, 2] = "MD Agent ID";
                            worksheet.Cells[1, 3] = "# of Agents Inputted Into System";
                            worksheet.Cells[1, 4] = "Target Active & Lapse Monthly Order(200 X Column D)";
                            worksheet.Cells[1, 5] = "Actual Active & Lapse Monthly Order";
                            worksheet.Cells[1, 6] = "Difference From Actual to Target";

                            for (int j = 2; j < allOrders[i].Count + 2; j++)
                            {
                                for (int k = 1; k < 7; k++)
                                {
                                    switch (k)
                                    {
                                        case 1:
                                            worksheet.Cells[j, k] = allOrders[i][j - 2].MarketingDirector;
                                            break;
                                        case 2:
                                            worksheet.Cells[j, k] = allOrders[i][j - 2].AccountId;
                                            break;
                                        case 3:
                                            worksheet.Cells[j, k] = allOrders[i][j - 2].AgentNumber;
                                            break;
                                        case 4:
                                            worksheet.Cells[j, k] = allOrders[i][j - 2].TargetCount;
                                            break;
                                        case 5:
                                            worksheet.Cells[j, k] = allOrders[i][j - 2].ActualCount;
                                            break;
                                        case 6:
                                            worksheet.Cells[j, k] = allOrders[i][j - 2].ActualCount - allOrders[i][j - 2].TargetCount;
                                            break;
                                        default:
                                            break;
                                    }
                                }
                            }
                        }
                    }

                    excelApplication.DisplayAlerts = false;
                    workbook.SaveCopyAs(path);
                }
            }
            catch (System.Exception ex)
            {
                Logger.Error("Exception occurs in generate excel report", ex);
                Mail adminMail = new Mail();
                adminMail.Subject =string.Format(Constants.MailSubjectException,"generating excel report");
                UserAccessor accessor = new UserAccessor();
                adminMail.Recipients = new List<string>() { "chris.li@akmii.com" };
                adminMail.Content =string.Format(Constants.MailContentException,"generating excel report",DateTime.Now.ToString(), "the target path not exist or target file is not given written permission, etc.");
                Utility.Send(adminMail);
            }
            finally
            {
                if (workbook != null)
                {
                    workbook.Close(false, o, o);
                }
                if (excelApplication.Workbooks != null)
                {
                    excelApplication.Workbooks.Close();
                }
                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                IntPtr point = new IntPtr(excelApplication.Hwnd);
                int processId = 0;
                GetWindowThreadProcessId(point, out processId);
                Process p = Process.GetProcessById(processId);
                p.Kill();
            }
        }

        /*
        public static void GenerateReport(List<OrderInfo> orderInfos, string path)
        {
            Application excelApplication = null;
            Workbook workbook = null;
            Worksheet worksheet = null;
            object o = System.Reflection.Missing.Value;

            try
            {
                if (orderInfos != null && orderInfos.Count > 0)
                {
                    excelApplication = new Application();
                    workbook = excelApplication.Workbooks.Add(o);

                    worksheet = (Worksheet)workbook.ActiveSheet;
                    worksheet.Name = "Order information";

                    ((Range)worksheet.Columns[3, Type.Missing]).ColumnWidth = 23;
                    ((Range)worksheet.Columns[4, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[5, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[6, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[7, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[8, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[9, Type.Missing]).ColumnWidth = 15;
                    ((Range)worksheet.Columns[10, Type.Missing]).ColumnWidth = 18;

                    worksheet.Cells[1, 1] = "Division";
                    worksheet.Cells[1, 2] = "Market";
                    worksheet.Cells[1, 3] = "Marketing Director Name";
                    worksheet.Cells[1, 4] = "# of Agents\r\nInputted Into\r\nSystem";
                    worksheet.Cells[1, 5] = "Active Lead\r\nCount";
                    worksheet.Cells[1, 6] = "Lapsed Lead\r\nCount";
                    worksheet.Cells[1, 7] = "Neighbor Lead\r\nCount";
                    worksheet.Cells[1, 8] = "Business Lead\r\nCount";
                    worksheet.Cells[1, 9] = "Affirmation\r\nApproval\r\n(Y/N)";
                    worksheet.Cells[1, 10] = "Date MD Submitted\r\nOrder";

                    for (int j = 2; j < orderInfos.Count + 2; j++)
                    {
                        for (int k = 1; k < 11; k++)
                        {
                            switch (k)
                            {
                                case 1:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].Division;
                                    break;
                                case 2:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].Market;
                                    break;
                                case 3:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AccountName;
                                    break;
                                case 4:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AgentNum.ToString();
                                    break;
                                case 5:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].ActiveLeadCount;
                                    break;
                                case 6:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].LapsedLeadCount;
                                    break;
                                case 7:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].NeighborLeadCount;
                                    break;
                                case 8:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].BusinessLeadCount;
                                    break;
                                case 9:
                                    worksheet.Cells[j, k] = "Y";
                                    break;
                                case 10:
                                    worksheet.Cells[j, k] = string.Format("{0}/{1}/{2}", orderInfos[j - 2].UpdateTS.Day,
                                         orderInfos[j - 2].UpdateTS.Month, orderInfos[j - 2].UpdateTS.Year);
                                    break;
                                default:
                                    break;
                            }
                        }
                    }

                    excelApplication.DisplayAlerts = false;
                    workbook.SaveCopyAs(path);
                }
            }
            catch (System.Exception ex)
            {
                Logger.Error("Exception occurs in generate excel report for order information", ex);
            }
            finally
            {
                if (workbook != null)
                {
                    workbook.Close(false, o, o);
                }
                if (excelApplication.Workbooks != null)
                {
                    excelApplication.Workbooks.Close();
                }
                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                IntPtr point = new IntPtr(excelApplication.Hwnd);
                int processId = 0;
                GetWindowThreadProcessId(point, out processId);
                Process p = Process.GetProcessById(processId);
                p.Kill();
            }
        }
        */

        public static void GenerateReport(List<OrderInfo> orderInfos, string path)
        {
            Application excelApplication = null;
            Workbook workbook = null;
            Worksheet worksheet = null;
            object o = System.Reflection.Missing.Value;

            try
            {
                if (orderInfos != null && orderInfos.Count > 0)
                {
                    excelApplication = new Application();
                    workbook = excelApplication.Workbooks.Add(o);

                    worksheet = (Worksheet)workbook.ActiveSheet;
                    worksheet.Name = "Order information";

                    ((Range)worksheet.Columns[3, Type.Missing]).ColumnWidth = 23;
                    ((Range)worksheet.Columns[4, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[5, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[6, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[7, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[8, Type.Missing]).ColumnWidth = 12;

                    worksheet.Cells[1, 1] = "Division";
                    worksheet.Cells[1, 2] = "Market";
                    worksheet.Cells[1, 3] = "Marketing Director Name";
                    worksheet.Cells[1, 4] = "# Agents Listed On\r\nAlpha Roster\r\nPer MD";
                    worksheet.Cells[1, 5] = "# Agents\r\nOrdered From\r\nAlpha Roster";
                    worksheet.Cells[1, 6] = "# Unique\r\nAgents\r\nCreated";
                    worksheet.Cells[1, 7] = "# Unique\r\nAgents\r\nOrdered";
                    worksheet.Cells[1, 8] = " Total #\r\nAssignments\r\nOrdered For\r\nAll Agents";
                    worksheet.Cells[1, 9] = "Active Lead\r\nCount";
                    worksheet.Cells[1, 10] = "Active Stop\r\nCount";
                    worksheet.Cells[1, 11] = "Lapsed Lead\r\nCount";
                    worksheet.Cells[1, 12] = "Lapsed Stop\r\nCount";
                    worksheet.Cells[1, 13] = "Neighbor Lead\r\nCount";
                    worksheet.Cells[1, 14] = "Neighbor Stop\r\nCount";
                    worksheet.Cells[1, 15] = "Business Lead\r\nCount";
                    worksheet.Cells[1, 16] = "Business Stop\r\nCount";
                    worksheet.Cells[1, 17] = "Affirmation\r\nApproval\r\n(Y/N)";
                    worksheet.Cells[1, 18] = "Date MD Submitted\r\nOrder";

                    for (int j = 2; j < orderInfos.Count + 2; j++)
                    {
                        for (int k = 1; k < 19; k++)
                        {
                            switch (k)
                            {
                                case 1:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].Division;
                                    break;
                                case 2:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].Market;
                                    break;
                                case 3:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AccountName;
                                    break;
                                case 4:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AgentAlphaRosterCount;
                                    break;
                                case 5:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AlphaRosterOrderCount;
                                    break;
                                case 6:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AgentCreatedCount;
                                    break;
                                case 7:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AgentCreatedOrderCount;
                                    break;
                                case 8:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].TotalAssignments;
                                    break;
                                case 9:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].ActiveLeadCount;
                                    break;
                                case 10:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].ActiveStopCount;
                                    break;
                                case 11:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].LapsedLeadCount;
                                    break;
                                case 12:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].LapsedStopCount;
                                    break;
                                case 13:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].NeighborLeadCount;
                                    break;
                                case 14:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].NeighborStopCount;
                                    break;
                                case 15:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].BusinessLeadCount;
                                    break;
                                case 16:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].BusinessStopCount;
                                    break;
                                case 17:
                                    worksheet.Cells[j, k] = "Y";
                                    break;
                                case 18:
                                    worksheet.Cells[j, k] = string.Format("{0}/{1}/{2}", orderInfos[j - 2].UpdateTS.Day,
                                         orderInfos[j - 2].UpdateTS.Month, orderInfos[j - 2].UpdateTS.Year);
                                    break;
                                default:
                                    break;
                            }
                        }
                    }

                    excelApplication.DisplayAlerts = false;
                    workbook.SaveCopyAs(path);
                }
            }
            catch (System.Exception ex)
            {
                Logger.Error("Exception occurs in generate excel report for order information", ex);
            }
            finally
            {
                if (workbook != null)
                {
                    workbook.Close(false, o, o);
                }
                if (excelApplication.Workbooks != null)
                {
                    excelApplication.Workbooks.Close();
                }
                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                IntPtr point = new IntPtr(excelApplication.Hwnd);
                int processId = 0;
                GetWindowThreadProcessId(point, out processId);
                Process p = Process.GetProcessById(processId);
                p.Kill();
            }
        }

        public static void GenerateReport(List<RepeatReason> reasons, string path)
        {
            Application excelApplication = null;
            Workbook workbook = null;
            Worksheet worksheet = null;
            object o = System.Reflection.Missing.Value;

            try
            {
                if (reasons != null && reasons.Count > 0)
                {
                    excelApplication = new Application();
                    workbook = excelApplication.Workbooks.Add(o);

                    worksheet = (Worksheet)workbook.ActiveSheet;
                    worksheet.Name = "Repeat Order";

                    ((Range)worksheet.Columns[1, Type.Missing]).ColumnWidth = 23;
                    ((Range)worksheet.Columns[2, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[3, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[4, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[5, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[6, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[7, Type.Missing]).ColumnWidth = 60;

                    worksheet.Cells[1, 1] = "Marketing Director Name";
                    worksheet.Cells[1, 2] = "Division";
                    worksheet.Cells[1, 3] = "Market";
                    worksheet.Cells[1, 4] = "City";
                    worksheet.Cells[1, 5] = "LeadBlockCode";
                    worksheet.Cells[1, 6] = "Assignment";
                    worksheet.Cells[1, 7] = "Reason";

                    for (int j = 2; j < reasons.Count + 2; j++)
                    {
                        for (int k = 1; k < 11; k++)
                        {
                            switch (k)
                            {
                                case 1:
                                    worksheet.Cells[j, k] = reasons[j - 2].MdNumber;
                                    break;
                                case 2:
                                    worksheet.Cells[j, k] = reasons[j - 2].Division;
                                    break;
                                case 3:
                                    worksheet.Cells[j, k] = reasons[j - 2].Market;
                                    break;
                                case 4:
                                    worksheet.Cells[j, k] = reasons[j - 2].City;
                                    break;
                                case 5:
                                    worksheet.Cells[j, k] = reasons[j - 2].LeadblockCode;
                                    break;
                                case 6:
                                    worksheet.Cells[j, k] = reasons[j - 2].AssignmentName;
                                    break;
                                case 7:
                                    worksheet.Cells[j, k] = reasons[j - 2].Reason;
                                    break;
                                default:
                                    break;
                            }
                        }
                    }

                    excelApplication.DisplayAlerts = false;
                    workbook.SaveCopyAs(path);
                }
            }
            catch (System.Exception ex)
            {
                Logger.Error("Exception occurs in generate excel report for repeat order", ex);
            }
            finally
            {
                if (workbook != null)
                {
                    workbook.Close(false, o, o);
                }
                if (excelApplication.Workbooks != null)
                {
                    excelApplication.Workbooks.Close();
                }
                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                IntPtr point = new IntPtr(excelApplication.Hwnd);
                int processId = 0;
                GetWindowThreadProcessId(point, out processId);
                Process p = Process.GetProcessById(processId);
                p.Kill();
            }
        }

        public static void GenerateReport(List<MarketDirector> users, string path)
        {
            Application excelApplication = null;
            Workbook workbook = null;
            Worksheet worksheet = null;
            object o = System.Reflection.Missing.Value;

            try
            {
                if (users != null && users.Count > 0)
                {
                    excelApplication = new Application();
                    workbook = excelApplication.Workbooks.Add(o);

                    worksheet = (Worksheet)workbook.ActiveSheet;
                    worksheet.Name = "Order information";

                    worksheet.Cells[1, 1] = "Marketing Director Code";
                    worksheet.Cells[1, 2] = "Active Lead Count";
                    worksheet.Cells[1, 3] = "Lapsed Lead Count";
                    worksheet.Cells[1, 4] = "Business Lead Count";
                    worksheet.Cells[1, 5] = "Neighbor Lead Count";
                    worksheet.Cells[1, 6] = "Total Lead Count";

                    for (int j = 2; j < users.Count + 2; j++)
                    {
                        for (int k = 1; k < 7; k++)
                        {
                            switch (k)
                            {
                                case 1:
                                    worksheet.Cells[j, k] = users[j - 2].Id;
                                    break;
                                case 2:
                                    worksheet.Cells[j, k] = users[j - 2].ActiveLeadCount;
                                    break;
                                case 3:
                                    worksheet.Cells[j, k] = users[j - 2].LapseLeadCount;
                                    break;
                                case 4:
                                    worksheet.Cells[j, k] = users[j - 2].BusinessLeadCount;
                                    break;
                                case 5:
                                    worksheet.Cells[j, k] = users[j - 2].NeighborLeadCount;
                                    break;
                                case 6:
                                    worksheet.Cells[j, k] = users[j - 2].TotalLeadCount;
                                    break;
                                default:
                                    break;
                            }
                        }
                    }

                    excelApplication.DisplayAlerts = false;
                    workbook.SaveCopyAs(path);
                }
            }
            catch (System.Exception ex)
            {
                Logger.Error("Exception occurs in generate excel report for md summary", ex);
            }
            finally
            {
                if (workbook != null)
                {
                    workbook.Close(false, o, o);
                }
                if (excelApplication.Workbooks != null)
                {
                    excelApplication.Workbooks.Close();
                }
                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                IntPtr point = new IntPtr(excelApplication.Hwnd);
                int processId = 0;
                GetWindowThreadProcessId(point, out processId);
                Process p = Process.GetProcessById(processId);
                p.Kill();
            }
        }

        public static void Send(Mail body)
        {
            SmtpClient client = new SmtpClient(SystemConfiguration.CommonSetting.SmtpServer, 25);
            client.DeliveryMethod = SmtpDeliveryMethod.Network;
            client.UseDefaultCredentials = false;
            client.Credentials = new NetworkCredential(SystemConfiguration.CommonSetting.SystemMailAddress,
                SystemConfiguration.CommonSetting.MailPassword);
            bool isExecute = true;
            int retryCount = 0;
            MailMessage message = null;

            while (isExecute)
            {
                try
                {
                    message = ConstructMailMessage(body);
                    client.Send(message);
                    isExecute = false;
                }
                catch (System.Exception ex)
                {
                    Logger.Error("Exception ocurrs in sending mail", ex);
                    retryCount++;

                    if (retryCount > 3)
                    {
                        isExecute = false;
                    }
                }
                finally
                {
                    if (message != null)
                    {
                        message.Dispose();
                        message = null;
                    }
                }
            }
        }

        public static string GetMonthName(int month)
        {
            switch (month)
            {
                case 1:
                    return "January";
                case 2:
                    return "February";
                case 3:
                    return "March";
                case 4:
                    return "April";
                case 5:
                    return "May";
                case 6:
                    return "June";
                case 7:
                    return "July";
                case 8:
                    return "August";
                case 9:
                    return "September";
                case 10:
                    return "October";
                case 11:
                    return "November";
                case 12:
                    return "December";
                default:
                    return "UNKOWN";
            }
        }

        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        private static extern int GetWindowThreadProcessId(IntPtr hwnd, out   int ID);

        private static MailMessage ConstructMailMessage(Mail body)
        {
            MailMessage message = new MailMessage();

            message.From = new MailAddress(SystemConfiguration.CommonSetting.SystemMailAddress);
            if (body.Recipients != null)
            {
                foreach (var address in body.Recipients)
                {
                    message.To.Add(new MailAddress(address));
                }
            }
            if (body.CarbonCopy != null)
            {
                foreach (var cc in body.CarbonCopy)
                {
                    message.CC.Add(cc);
                }
            }

            message.Subject = body.Subject;
            message.Body = body.Content;

            if (!string.IsNullOrEmpty(body.AttachmentFile))
            {
                if (System.IO.File.Exists(body.AttachmentFile))
                {
                    message.Attachments.Add(new Attachment(body.AttachmentFile));
                }
            }

            return message;
        }

        private static string GetZipEntryName(string filePath, bool removePath)
        {
            string r = filePath;
            if (removePath)
            {
                if (removePath)
                {
                    int i = r.LastIndexOf('\\');
                    if (i < 0)
                    {
                        i = r.LastIndexOf('/');
                    }
                    if (i >= 0)
                    {
                        r = r.Substring(i + 1);
                    }
                }
            }
            return r;
        }
    }
}
